package com.dsrk.sysusage;

import java.io.*;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.*;

import java.sql.*;

public class Display extends HttpServlet implements Servlet {
	static final long serialVersionUID = 42L;
	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		try {
			InitialContext ctx = new InitialContext();
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/MyDataSource");
			java.sql.Connection conn = ds.getConnection();

			Statement st = conn.createStatement();
			ResultSet rs = st
					.executeQuery(""
							+ " SELECT n1,"
							+ "   n3,"
							+ "   n4,"
							+ "   t4,"
							+ "   n4/1.0/t4"
							+ " FROM ("
							+ "     SELECT HOSTNAME,"
							+ "       PROPERTY,"
							+ "       CHAR(DATE(TIMESTAMP))||' 0'||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00',"
							+ "       AVG(VALUE)"
							+ "     FROM SYSUSAGE_RAWDATA"
							+ "     WHERE PROPERTY = 'nic'"
							+ "       AND HOUR(TIMESTAMP)<10"
							+ "     GROUP BY HOSTNAME, PROPERTY, CHAR(DATE(TIMESTAMP))||' 0'||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00'"
							+ "     UNION"
							+ "     SELECT HOSTNAME,"
							+ "       PROPERTY,"
							+ "       CHAR(DATE(TIMESTAMP))||' '||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00',"
							+ "       AVG(VALUE)"
							+ "     FROM SYSUSAGE_RAWDATA"
							+ "     WHERE PROPERTY = 'nic'"
							+ "       AND HOUR(TIMESTAMP)>=10"
							+ "     GROUP BY HOSTNAME, PROPERTY, CHAR(DATE(TIMESTAMP))||' '||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00') NIC(n1, n2, n3, n4),"
							+ "   ("
							+ "     SELECT HOSTNAME,"
							+ "       PROPERTY,"
							+ "       CHAR(DATE(TIMESTAMP))||' 0'||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00',"
							+ "       AVG(VALUE)"
							+ "     FROM SYSUSAGE_RAWDATA"
							+ "     WHERE PROPERTY = 'nic_tot'"
							+ "       AND HOUR(TIMESTAMP)<10"
							+ "     GROUP BY HOSTNAME, PROPERTY, CHAR(DATE(TIMESTAMP))||' 0'||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00'"
							+ "     UNION"
							+ "     SELECT HOSTNAME,"
							+ "       PROPERTY,"
							+ "       CHAR(DATE(TIMESTAMP))||' '||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00',"
							+ "       AVG(VALUE)"
							+ "     FROM SYSUSAGE_RAWDATA"
							+ "     WHERE PROPERTY = 'nic_tot'"
							+ "       AND HOUR(TIMESTAMP)>=10"
							+ "     GROUP BY HOSTNAME, PROPERTY, CHAR(DATE(TIMESTAMP))||' '||RTRIM(CHAR(HOUR(TIMESTAMP)))||':00') NIC_TOT(t1, t2, t3, t4)"
							+ " WHERE n1=t1" + "   AND n3=t3"
							+ " ORDER BY n1, n3");
			ServletOutputStream out = resp.getOutputStream();

			out.print("HaHa");
			dumpData(rs, out);
		} catch (Exception e) {
			System.err.println(e);
			e.printStackTrace();
		}

	}

	private int dumpData(java.sql.ResultSet rs,
			javax.servlet.ServletOutputStream out) throws Exception {
		int rowCount = 0;

		out.println("<P ALIGN='center'><TABLE BORDER=1>");
		ResultSetMetaData rsmd = rs.getMetaData();
		int columnCount = rsmd.getColumnCount();
		// table header
		out.println("<TR>");
		for (int i = 0; i < columnCount; i++) {
			out.println("<TH>" + rsmd.getColumnLabel(i + 1) + "</TH>");
		}
		out.println("</TR>");
		// the data
		while (rs.next()) {
			rowCount++;
			out.println("<TR>");
			for (int i = 0; i < columnCount; i++) {
				out.println("<TD>" + rs.getString(i + 1) + "</TD>");
			}
			out.println("</TR>");
		}
		out.println("</TABLE></P>");
		return rowCount;
	}

}
